﻿/*============================================================================
  File:    Simple Sample AMO add features to db.cs

  Summary: Contains the code to add the cool features of AS to 
           the existing starting database

           Part of Simple Sample AMO Access

  Date:    November 2006
------------------------------------------------------------------------------
  This file is part of the Microsoft SQL Server Code Samples.

  Copyright (C) Microsoft Corporation.  All rights reserved.

  This source code is intended only as a supplement to Microsoft
  Development Tools and/or on-line documentation.  See these other
  materials for detailed information regarding Microsoft code samples.

  THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY
  KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE
  IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
  PARTICULAR PURPOSE.
============================================================================*/

#region Using directives

using System;
using System.Collections;
using System.Collections.Generic;
using System.Configuration;
using System.Diagnostics;
using System.Globalization;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.Data.Sql;
using Microsoft.AnalysisServices;

#endregion

namespace Microsoft.Samples.SqlServer
{
    public partial class SimpleSampleAMOAccess
    {
        public void AddFeaturesToDatabase()
        {
            Database db = svr.Databases[DatabaseName];
            Cube cube = db.Cubes[CubeName];

            #region Create cube features
            CreateActions(cube);
            CreateKPIs(cube);
            CreatePerspectives(cube);
            #endregion

            cube.Update(UpdateOptions.ExpandFull);
            

            #region Design Aggregations for measure groups
            String AggregationsDesigned = "";

            Debug.WriteLine(String.Format("Designing aggregations just started"));

            AggregationsDesigned = DesignAggregationsOnPartitions(cube.MeasureGroups.GetByName("Customers"), 5.0, 50000.0, 500);
            Debug.WriteLine(String.Format("Aggregations Designed for [Customers] MeasureGroup: {0}", AggregationsDesigned));

            AggregationsDesigned = DesignAggregationsOnPartitions(cube.MeasureGroups.GetByName("Currency Rates"), 10.0, 100000.0, 1000);
            Debug.WriteLine(String.Format("Aggregations Designed for [Currency Rates] MeasureGroup: {0}", AggregationsDesigned));

            AggregationsDesigned = DesignAggregationsOnPartitions(cube.MeasureGroups.GetByName("Sales Reasons"), 20.0, 200000.0, 2000);
            Debug.WriteLine(String.Format("Aggregations Designed for [Sales Reasons] MeasureGroup: {0}", AggregationsDesigned));

            AggregationsDesigned = DesignAggregationsOnPartitions(cube.MeasureGroups.GetByName("Internet Sales"), 30.0, 300000.0, 3000);
            Debug.WriteLine(String.Format("Aggregations Designed for [Internet Sales] MeasureGroup: {0}", AggregationsDesigned));

            AggregationsDesigned = DesignAggregationsOnPartitions(cube.MeasureGroups.GetByName("Reseller Sales"), 40.0, 400000.0, 4000);
            Debug.WriteLine(String.Format("Aggregations Designed for [Reseller Sales] MeasureGroup: {0}", AggregationsDesigned));

            cube.Update();
            #endregion


            // Create translations
            CreateTranslations(db);
            db.Update();

            // Create the cache
            CreateProactiveCache(db);
            db.Update();

            // Create the stored procedures
            CreateStoredProcedures(db);
            db.Update();

            // Create the roles and permissions
            CreateRolesAndPermissions(db);
            db.Update();

            // BackUp the database
            BackupInfo backUpInfo = new BackupInfo();
            backUpInfo.AllowOverwrite = true;
            backUpInfo.ApplyCompression = true;
            backUpInfo.File = String.Concat(CubeName,".ABF");
            db.Backup(backUpInfo);

        }

        static void CreateStoredProcedures(Database db)
        {
            Debug.WriteLine(String.Format("starting... CreateStoredProcedures(db:{0})", db.Name));
            // Create the CLR assembly
            ClrAssembly clrAssembly = db.Assemblies.Add("StoredProcedures");
            clrAssembly.ImpersonationInfo = new ImpersonationInfo(
                ImpersonationMode.ImpersonateServiceAccount);
            clrAssembly.PermissionSet = PermissionSet.Unrestricted;

            // Load the assembly files
            clrAssembly.LoadFiles(Environment.CurrentDirectory
                + @"\Simple Sample Stored Procedures.dll", false);

            clrAssembly.Update();
            Debug.WriteLine(String.Format("ending... CreateStoredProcedures(db:{0})", db.Name));
        }



        static void CreateRolesAndPermissions(Database db)
        {
            Debug.WriteLine(String.Format("starting... CreateRolesAndPermissions(db:{0})", db.Name));
            Cube cube = db.Cubes[0];
            Role role;
            DatabasePermission dbperm;
            CubePermission cubeperm;

            #region Create the Database Administrators role

            // Create the Database Administrators role.
            role = db.Roles.Add("Database Administrators");
            role.Members.Add(new RoleMember("")); // e.g. redmond\johndoe
            role.Update();

            // Assign administrative permissions to this role.
            // Members of this role can perform any operation within the database.
            dbperm = db.DatabasePermissions.Add(role.ID);
            dbperm.Administer = true;
            dbperm.Update();

            #endregion

            #region Create the Processors role

            // Create the Processors role.
            role = db.Roles.Add("Processors");
            role.Members.Add(new RoleMember("")); // e.g. redmond\johndoe
            role.Update();

            // Assign Read and Process permissions to this role.
            // Members of this role can process objects in the database and query them to verify results.
            // Process permission applies to all contained objects, i.e. all dimensions and cubes.
            // Read permission does not apply to contained objects, so we must assign the permission explicitly on the cubes.
            dbperm = db.DatabasePermissions.Add(role.ID);
            dbperm.Read = ReadAccess.Allowed;
            dbperm.Process = true;
            dbperm.Update();

            cubeperm = cube.CubePermissions.Add(role.ID);
            cubeperm.Read = ReadAccess.Allowed;
            cubeperm.Update();

            #endregion

            #region Create the Writers role

            // Create the Writers role.
            role = db.Roles.Add("Writers");
            role.Members.Add(new RoleMember("")); // e.g. redmond\johndoe
            role.Update();

            // Assign Read and Write permissions to this role.
            // Members of this role can discover, query and writeback to the Adventure Works cube.
            // However cell access and writeback is restricted to the United States (in the Customer dimension).
            dbperm = db.DatabasePermissions.Add(role.ID);
            dbperm.Read = ReadAccess.Allowed;
            dbperm.Update();

            cubeperm = cube.CubePermissions.Add(role.ID);
            cubeperm.Read = ReadAccess.Allowed;
            cubeperm.Write = WriteAccess.Allowed;
            cubeperm.CellPermissions.Add(new CellPermission(CellPermissionAccess.Read, "[Customer].[Country-Region].CurrentMember is [Customer].[Country-Region].[Country-Region].&[United States]"));
            cubeperm.CellPermissions.Add(new CellPermission(CellPermissionAccess.ReadWrite, "[Customer].[Country-Region].CurrentMember is [Customer].[Country-Region].[Country-Region].&[United States]"));
            cubeperm.Update();

            #endregion

            #region Create the Readers role

            // Create the Readers role.
            role = db.Roles.Add("Readers");
            role.Members.Add(new RoleMember("")); // e.g. redmond\johndoe
            role.Update();

            // Assign Read permissions to this role.
            // Members of this role can discover and query the Adventure Works cube.
            // However the Customer dimension is restricted to the United States.
            dbperm = db.DatabasePermissions.Add(role.ID);
            dbperm.Read = ReadAccess.Allowed;
            dbperm.Update();

            cubeperm = cube.CubePermissions.Add(role.ID);
            cubeperm.Read = ReadAccess.Allowed;
            Dimension dim = db.Dimensions.GetByName("Customer");
            DimensionAttribute attr = dim.Attributes.GetByName("Country-Region");
            CubeDimensionPermission cubedimperm = cubeperm.DimensionPermissions.Add(dim.ID);
            cubedimperm.Read = ReadAccess.Allowed;
            AttributePermission attrperm = cubedimperm.AttributePermissions.Add(attr.ID);
            attrperm.AllowedSet = "{[Customer].[Country-Region].[Country-Region].&[United States]}";
            cubeperm.Update();

            #endregion
            Debug.WriteLine(String.Format("ending... CreateRolesAndPermissions(db:{0})", db.Name));
        }

        static void CreateActions(Cube cube)
        {
            Debug.WriteLine(String.Format("starting... CreateActions(cube:{0})", cube.Name));
            #region Adding a drillthrough action
            //Create a Drillthrough action
            DrillThroughAction dtAction = new DrillThroughAction("Reseller Details", "Drillthrough Action");

            //Define the Action
            dtAction.Invocation = ActionInvocation.Interactive;
            dtAction.Type = ActionType.DrillThrough;
            dtAction.TargetType = ActionTargetType.Cells;
            dtAction.Target = "MeasureGroupMeasures(\"Reseller Sales\")";
            dtAction.Caption = "DrillThrough...";
            dtAction.CaptionIsMdx = false;


            //Adding Measure columns
            MeasureBinding mb1 = new MeasureBinding();
            mb1.MeasureID = "Reseller Sales Amount";
            dtAction.Columns.Add(mb1);
            

            MeasureBinding mb2 = new MeasureBinding();
            mb2.MeasureID = "Reseller Order Quantity";
            dtAction.Columns.Add(mb2);

            MeasureBinding mb3 = new MeasureBinding();
            mb3.MeasureID = "Reseller Unit Price";
            dtAction.Columns.Add(mb3);

            //Adding Dimension Columns
            CubeAttributeBinding cb1 = new CubeAttributeBinding();
            cb1.CubeID = cube.ID;
            cb1.CubeDimensionID = "Reseller";
            cb1.AttributeID = "Reseller";
            cb1.Type = AttributeBindingType.All;
            dtAction.Columns.Add(cb1);

            CubeAttributeBinding cb2 = new CubeAttributeBinding();
            cb2.CubeID = cube.ID;
            cb2.CubeDimensionID = "Product";
            cb2.AttributeID = "Product Name";
            cb2.Type = AttributeBindingType.All;
            dtAction.Columns.Add(cb2);

            //Add the defined action to the cube
            cube.Actions.Add(dtAction);
            #endregion

            #region adding a Standard action
            StandardAction stAction = new StandardAction("HTML Action", "HTML Action");

            //Define the Action
            stAction.Invocation = ActionInvocation.Interactive;
            stAction.Type = ActionType.Html;
            stAction.TargetType = ActionTargetType.Cube;
            stAction.Target = cube.Name;
            stAction.Caption = "HTML Action over [" + cube.Name + "] cube";
            stAction.CaptionIsMdx = false;
            stAction.Expression = "http://msdn.microsoft.com";


            //Add the defined action to the cube
            cube.Actions.Add(stAction);
            #endregion


            Debug.WriteLine(String.Format("ending... CreateActions(cube:{0})", cube.Name));
        }

        static void CreateKPIs(Cube cube)
        {
            Debug.WriteLine(String.Format("starting... CreateKPIs(cube:{0})", cube.Name));
            Kpi kpi = cube.Kpis.Add("Internet Revenue", "Internet Revenue");
            kpi.Description = "Revenue realized through direct sales via Interner";
            kpi.DisplayFolder = "\\Financial Perspective\\Grow Revenue";
            kpi.AssociatedMeasureGroupID = "Internet Sales";
            kpi.Value = "[Measures].[Internet Sales Amount]";
            #region Goal
            kpi.Goal = "Case" +
                       "     When IsEmpty" +
                       "          (" +
                       "            ParallelPeriod" +
                       "            (" +
                       "              [Date].[Fiscal Time].[Fiscal Year]," +
                       "              1," +
                       "              [Date].[Fiscal Time].CurrentMember" +
                       "            )" +
                       "          )" + 
                       "     Then [Measures].[Internet Sales Amount]" +
                       "     Else 1.10 *" +
                       "          (" +
                       "            [Measures].[Internet Sales Amount]," +
                       "            ParallelPeriod" +
                       "            (" +
                       "              [Date].[Fiscal Time].[Fiscal Year]," +
                       "              1," +
                       "              [Date].[Fiscal Time].CurrentMember" +
                       "            )" +
                       "          ) " +
                       " End";
            #endregion
            #region Status
            kpi.Status = "Case" +
                         "   When KpiValue( \"Internet Revenue\" ) / KpiGoal ( \"Internet Revenue\" ) >= .95 " +
                         "   Then 1 " +
                         "   When KpiValue( \"Internet Revenue\" ) / KpiGoal ( \"Internet Revenue\" ) <  .95 " +
                         "        And  " +
                         "        KpiValue( \"Internet Revenue\" ) / KpiGoal ( \"Internet Revenue\" ) >= .85 " +
                         "   Then 0 " +
                         "   Else -1 " +
                         "End";
            #endregion
            #region Trend
            kpi.Trend = "Case " +
                        "    When VBA!Abs " +
                        "         ( " +
                        "           KpiValue( \"Internet Revenue\" ) -  " +
                        "           ( " +
                        "             KpiValue ( \"Internet Revenue\" ), " +
                        "             ParallelPeriod " +
                        "             (  " +
                        "               [Date].[Fiscal Time].[Fiscal Year], " +
                        "               1, " +
                        "               [Date].[Fiscal Time].CurrentMember " +
                        "             ) " +
                        "           ) / " +
                        "           ( " +
                        "             KpiValue ( \"Internet Revenue\" ), " +
                        "             ParallelPeriod " +
                        "             (  " +
                        "               [Date].[Fiscal Time].[Fiscal Year], " +
                        "               1, " +
                        "               [Date].[Fiscal Time].CurrentMember " +
                        "             ) " +
                        "           )   " +
                        "         ) <=.02  " +
                        "    Then 0 " +
                        "    When KpiValue( \"Internet Revenue\" ) -  " +
                        "         ( " +
                        "           KpiValue ( \"Internet Revenue\" ), " +
                        "           ParallelPeriod " +
                        "           (  " +
                        "             [Date].[Fiscal Time].[Fiscal Year], " +
                        "             1, " +
                        "             [Date].[Fiscal Time].CurrentMember " +
                        "           ) " +
                        "         ) / " +
                        "         ( " +
                        "           KpiValue ( \"Internet Revenue\" ), " +
                        "           ParallelPeriod " +
                        "           (  " +
                        "             [Date].[Fiscal Time].[Fiscal Year], " +
                        "             1, " +
                        "             [Date].[Fiscal Time].CurrentMember " +
                        "           ) " +
                        "         )  >.02 " +
                        "    Then 1 " +
                        "    Else -1 " +
                        "End";
            #endregion
            kpi.TrendGraphic = "Standard Arrow";
            kpi.StatusGraphic = "Cylinder";
            Debug.WriteLine(String.Format("ending... CreateKPIs(cube:{0})", cube.Name));
        }

        static void CreatePerspectives(Cube cube)
        {
            Debug.WriteLine(String.Format("starting... CreatePerspectives(cube:{0})", cube.Name));
            Perspective perspective = cube.Perspectives.Add("Direct Sales", "Direct Sales");
            CubeDimension dim1 = cube.Dimensions.GetByName("Date");
            PerspectiveDimension pdim1 = perspective.Dimensions.Add(dim1.DimensionID);
            pdim1.Attributes.Add("Date");
            pdim1.Attributes.Add("Calendar Year");
            pdim1.Attributes.Add("Fiscal Year");
            pdim1.Attributes.Add("Calendar Quarter");
            pdim1.Attributes.Add("Fiscal Quarter");
            pdim1.Attributes.Add("Calendar Month Number");
            pdim1.Attributes.Add("Fiscal Month Number");
            pdim1.Hierarchies.Add("Calendar Time");
            pdim1.Hierarchies.Add("Fiscal Time");

            CubeDimension dim2 = cube.Dimensions.GetByName("Product");
            PerspectiveDimension pdim2 = perspective.Dimensions.Add(dim2.DimensionID);
            pdim2.Attributes.Add("Product Name");
            pdim2.Attributes.Add("Product Line");
            pdim2.Attributes.Add("Model Name");
            pdim2.Attributes.Add("List Price");
            pdim2.Attributes.Add("Size");
            pdim2.Attributes.Add("Weight");
            pdim2.Hierarchies.Add("Product Model Categories");
            pdim2.Hierarchies.Add("Product Categories");

            PerspectiveMeasureGroup pmg = perspective.MeasureGroups.Add("Internet Sales");
            pmg.Measures.Add("Internet Sales Amount");
            pmg.Measures.Add("Internet Order Quantity");
            pmg.Measures.Add("Internet Unit Price");

            pmg = perspective.MeasureGroups.Add("Reseller Sales");
            pmg.Measures.Add("Reseler Sales Amount");
            pmg.Measures.Add("Reseller Order Quantity");
            pmg.Measures.Add("Reseller Unit Price");

            PerspectiveAction pact = perspective.Actions.Add("Drillthrough Action");

            PerspectiveKpi pkpi = perspective.Kpis.Add("Internet Revenue");
            Debug.WriteLine(String.Format("ending... CreatePerspectives(cube:{0})", cube.Name));
        }
        static void CreateProactiveCache(Database db)
        {
            Debug.WriteLine(String.Format("starting... CreateProactiveCache(db:{0})", db.Name));
            Dimension dim = db.Dimensions.GetByName("Date");
            ProactiveCaching pc = new ProactiveCaching();
            
            pc.SilenceInterval = TimeSpan.FromSeconds(10);
            pc.Latency = TimeSpan.FromSeconds(-1);
            pc.SilenceOverrideInterval = TimeSpan.FromMinutes(10);
            pc.ForceRebuildInterval = TimeSpan.FromSeconds(-1);
            pc.Enabled = true;
            pc.OnlineMode = ProactiveCachingOnlineMode.OnCacheComplete;
            ProactiveCachingTablesBinding pctb = new ProactiveCachingTablesBinding();
            pctb.NotificationTechnique = NotificationTechnique.Server;
            TableNotification tn = new TableNotification("DimTime", "dbo");
            pctb.TableNotifications.Add( tn);
            pc.Source = pctb;

            dim.ProactiveCaching = pc;
            dim.Update();
            Debug.WriteLine(String.Format("ending... CreateProactiveCache(db:{0})", db.Name));
        }

        static void CreateTranslations(Database db)
        {
            Debug.WriteLine(String.Format("starting... CreateTranslations(db:{0})", db.Name));
            //Spanish Tranlations for Product Category in Product Dimension
            Dimension dim = db.Dimensions["Product"];
            DimensionAttribute atr = dim.Attributes["Product Name"];
            Translation tran = atr.Translations.Add(3082);
            tran.Caption = "Nombre Producto";

            dim.Update(UpdateOptions.ExpandFull);
            Debug.WriteLine(String.Format("ending... CreateTranslations(db:{0})", db.Name));

        }

        static public String DesignAggregationsOnPartitions(MeasureGroup mg, double optimizationWanted, double maxStorageBytes, long maxWaitMilliSeconds)
        {
            double optimization = 0;
            double storage = 0;
            long executionMilliSeconds=0;
            bool finished = false;
            AggregationDesign ad = null;
            DesignAggregationsResult dar = null;
            String aggDesignName;
            String AggregationsDesigned = "";

            Debug.WriteLine(String.Format("Starting... DesignAggregationsOnPartitions(MeasureGroup:{0}, optimizationWanted:{1}, maxStorageBytes:{2})", mg.Name, optimizationWanted, maxStorageBytes));


            //Obtain estimated number of members per dimension attribute
            DataSourceView mgDsv = mg.Parent.DataSourceView;
            
            foreach (MeasureGroupDimension mgDim in mg.Dimensions)
            {
                Dimension dimension = mgDim.Dimension;
                DataSourceView dimensionDsv = dimension.DataSourceView;
                if ( dimensionDsv == null )
                    throw new Exception("Dimension DSV is not set, cannot do the members counting !");
                DataSet schema = dimensionDsv.Schema;
                if (schema == null)
                    throw new Exception("Dimension Schema is not set, cannot do the members counting !");
                
                foreach (DimensionAttribute dimAttr in dimension.Attributes)
                {
                    string[] columnNames = new String[dimAttr.KeyColumns.Count];
                    string tableName = null;
                    int i = 0;
                    foreach (DataItem column in dimAttr.KeyColumns)
                    {
                        ColumnBinding cb = (ColumnBinding)column.Source;
                        if (cb == null)
                            throw new Exception("Binding not properly set for KeyColumn #" + i + " !");

                        if ( tableName == null )
                            tableName = GetSqlTableExpression(schema, cb, "[", "]"); // use different quotes for different relational datasource providers

                        columnNames[i++] = GetSqlColumnExpression(schema, cb, "[", "]"); // use different quotes for different relational datasource providers
                    }
                    
                    if ( dimAttr.EstimatedCount == 0 )
                        dimAttr.EstimatedCount = GetEstimatedNumberOfMembers(dimensionDsv, tableName, columnNames, true);
                }

                dimension.Update();
            }

            //Obtain estimated number of rows of MeasureGroup
            ColumnBinding mcb = (ColumnBinding) mg.Measures[0].Source.Source;
            String mgTableName = GetSqlTableExpression(mgDsv.Schema, mcb, "[", "]"); // use different quotes for different relational datasource providers;
            if (mg.EstimatedRows == 0 )
                mg.EstimatedRows = GetEstimatedNumberOfRows(mgDsv, mgTableName, "");
            //Update counts to MeasureGroup
            mg.Update();

            aggDesignName = mg.AggregationPrefix + "_" + mg.Name;
            ad = mg.AggregationDesigns.Add();
            ad.Name = aggDesignName;
            ad.InitializeDesign();
            while ((!finished) && (optimization < optimizationWanted) && (storage < maxStorageBytes) && (executionMilliSeconds < maxWaitMilliSeconds))
            {
                //ad.DesignAggregations(out optimization, out storage, out aggCount, out finished);
                dar = ad.DesignAggregations(new TimeSpan(0, 0, 1), 10, optimizationWanted, long.Parse( maxStorageBytes.ToString()), new System.Collections.Specialized.StringCollection());
                optimization = dar.Optimization;
                storage = dar.Storage;
                executionMilliSeconds = dar.Time;
                finished = dar.LastStep;
            }
            ad.FinalizeDesign();
            foreach (Partition part in mg.Partitions)
            {
                part.AggregationDesignID = ad.ID;
                AggregationsDesigned += aggDesignName + " = " + dar.AggregationCount.ToString() + " aggregations designed\r\n\tOptimization: " + optimization.ToString() + "/" + optimizationWanted.ToString() + "\n\r\tStorage: " + storage.ToString() + "/" + maxStorageBytes.ToString() + " ]\n\r";
            }
            Debug.WriteLine(String.Format("Ending... DesignAggregationsOnPartitions(MeasureGroup:{0}, optimizationWanted:{1}, maxStorageBytes:{2})= {3}", mg.Name, optimizationWanted, maxStorageBytes, AggregationsDesigned));

            return AggregationsDesigned;
        }

    }
}
